package com.xsz.service;

import com.xsz.dto.JobDTO;
import com.xsz.dto.ResumePostionDto;
import com.xsz.dto.RoleDTO;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import java.util.Map;

@Repository
public class DTODao {
    @Resource
    private JdbcTemplate jdbcTemplate;

    //投简历
    public int addPositionResume(JobDTO jobDTO){
        String sql="insert into tb_possition_resume(resume_id,pid) values ('"+jobDTO.getRid()+"','"+jobDTO.getPid()+"')";
        return jdbcTemplate.update(sql);
    }

    //切换角色
    public int switchrole(RoleDTO roleDTO){
        String sql="update tb_user_role set role_id='"+roleDTO.getRid()+"' where uid='"+roleDTO.getUid()+"'";
        return jdbcTemplate.update(sql);
    }


    //根据简历表和职位表显示查询到的申请人名字和申请的职位
    public List<ResumePostionDto> showCreatePname(){
        String sql="select tb_resume.id,tb_resume.create_by,tb_position.name from tb_position,tb_resume,tb_position_resume where tb_position.id=tb_position_resume.pid and tb_resume.id=tb_position_resume.resume_id;";
        List<ResumePostionDto> ls=null;
        RowMapper<ResumePostionDto> rl=new RowMapper<ResumePostionDto>() {
            @Override
            public ResumePostionDto mapRow(ResultSet rs, int rowNum) throws SQLException {
                ResumePostionDto rd=new ResumePostionDto();
                rd.setCreateBy(rs.getString("create_by"));
                rd.setDeliverId(rs.getString("id"));
                rd.setPname(rs.getString("name"));
                return rd;
            }
        };
        ls=jdbcTemplate.query(sql, rl);
        return ls;

    }

    public List<Map<String, Object>> queryHomeWorkDTOListMap(String homework_id) {

        String sql="select u.name,uh.homework_id from user u left join user_homework uh on (u.id=uh.user_id and uh.homework_id=?)";
        // 参数数组，按照顺序放入sql中的？占位符
        Object[] args = { homework_id };
        // 参数数组数据类型，跟上面数组的参数顺序对应，用来设置参数的数据类型
        int[] argTypes = { Types.VARCHAR };

        return jdbcTemplate.queryForList(sql, args, argTypes);

    }


    public List<Map<String, Object>> getHomewokmasterByDateAndType(long sreachDate,String type){
        String sql="select * from homeworkmaster where open_date=? and type=?";
        Date date=new Date(sreachDate);
        // 参数数组，按照顺序放入sql中的？占位符
        Object[] args = { date,type };
        // 参数数组数据类型，跟上面数组的参数顺序对应，用来设置参数的数据类型
        int[] argTypes = {Types.DATE, Types.VARCHAR };

        return jdbcTemplate.queryForList(sql, args, argTypes);

    }


}



